<%@ page language="java" import="java.util.*,java.sql.*"
	pageEncoding="utf-8"%>
<%@page import="com.bean.LoginBean"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>管理页面</title>
<!-- 调用CSS，JS -->
<link href="/officeoa/admin/images/style.css" rel="stylesheet"
	type="text/css" />
<style type="text/css">
<!--
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
	font-family: "宋体";
	font-size: 12px;
	color: #333333;
	background-color: ;
}

.STYLE1 {
	color: #333333;
	font-size: x-large;
	font-family: Arial, Helvetica, sans-serif;
}
-->
</style>


</head>
<body>
	<%
		//定义四个分页会用到的变量
		int pageSize = 5;
		int pageNow = 1;//默认显示第一页
		int rowCount = 0;//该值从数据库中查询
		int pageCount = 0;//该值是通过pageSize和rowCount
		LoginBean LoginBean = (LoginBean) session.getAttribute("Loginbean");
		String key = request.getParameter("key")==null?"":request.getParameter("key");
		String dept=LoginBean.getDept();
		String s_pageNow = request.getParameter("pageNow");
		if (s_pageNow != null) {
			//接收到了pageNow
			pageNow = Integer.parseInt(s_pageNow);
		}
		//查询得到rowCount
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String url = "jdbc:mysql://localhost:3306/graduatedesign";
		String user = "root";
		String password = "root";
		Connection conn = DriverManager.getConnection(url, user, password);
		Statement sm = conn.createStatement();
		String sql1="select COUNT(DISTINCT kaoqin.username  )from kaoqin,userinfo where  kaoqin.username=userinfo.username and userinfo.dept='"+dept+"'";
		if(!key.equals(""))
     	{
     		sql1+=" and kaoqin.username like '%"+key+"%' ";
     	}
		ResultSet rs = sm.executeQuery(sql1);

		if (rs.next()) {
			rowCount = rs.getInt(1);
		}
		//计算pageCount
		if (rowCount % pageSize == 0) {
			pageCount = rowCount / pageSize;
		} else {
			pageCount = rowCount / pageSize + 1;
		}
		//查询出需要显示的记录
		String sql2="select distinct(kaoqin.username),sum( case when date like '2017-01%' then 1 else 0 end),sum( case when date like '2017-02%' then 1 else 0 end),sum( case when date like '2017-03%' then 1 else 0 end),sum( case when date like '2017-04%' then 1 else 0 end),sum( case when date like '2017-05%' then 1 else 0 end),sum( case when date like '2017-06%' then 1 else 0 end),sum( case when date like '2017-07%' then 1 else 0 end),sum( case when date like '2017-08%' then 1 else 0 end),sum( case when date like '2017-09%' then 1 else 0 end),sum( case when date like '2017-10%' then 1 else 0 end),sum( case when date like '2017-11%' then 1 else 0 end),sum( case when date like '2017-12%' then 1 else 0 end) from kaoqin,userinfo where userinfo.username=kaoqin.username and userinfo.dept='"+dept+"'";
		if(!key.equals(""))
     	{
     		sql2+=" and kaoqin.username like '%"+key+"%'";
     	}
     	sql2+=" group by kaoqin.username limit " + (pageNow-1)*pageSize+","+ pageSize;
		rs=sm.executeQuery(sql2);
	%>
	
	<script type="text/javascript"
		src="/officeoa/commfiles/js/calendar/WdatePicker.js"></script>



	<form id="form1" name="form1" method="post" action="kaoqtj.jsp">
		<table width="100%" border="0" cellpadding="0" cellspacing="1"
			bgcolor="#BBD3EB">
			<tr>
				<td height="26" align="center" bgcolor="#FFFFFF">请输入相关信息</td>
				<td height="26" align="left" bgcolor="#FFFFFF">按人员查询： &nbsp;<input
					name="key" type="text" value="<%=key %>" id="textfield" />&nbsp; <input
					type="submit" name="button" id="button" value="查询" /></td>
			</tr>
		</table>

		<br /> <br />


		<table width="100%" border="0" cellpadding="0" cellspacing="1"
			bgcolor="#BBD3EB">
			<tr>
				<td height="27" align="center"
					background="/officeoa/admin/images/index1_72.gif" bgcolor="#FFFFFF">人员</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">1月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">2月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">3月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">4月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">5月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">6月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">7月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">8月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">9月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">10月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">11月</td>
				<td align="center" background="/officeoa/admin/images/index1_72.gif"
					bgcolor="#FFFFFF">12月</td>
			</tr>

			<%
				while (rs.next()) {
			%>

			<tr>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getString(1) %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(2)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(3)    %> </td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(4)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(5)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(6)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(7)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(8)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(9)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(10)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(11)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(12)    %></td>
				<td height="26" align="center" bgcolor="#FFFFFF"><%=rs.getInt(13)    %></td>
			</tr>
			<%
				}
			%>

			<tr>
				<td height="26" colspan="13" align="center" bgcolor="#FFFFFF">
					<%
						//上一页
						if (pageNow != 1) {
							out.println("<a href=kaoqtj.jsp?pageNow="
									+ (pageNow - 1) + ">上一页</a>");
						}
						//显示超链接
						for (int i = 1; i <= pageCount; i++) {
							out.println("<a href=kaoqtj.jsp?pageNow=" + i + ">["
									+ i + "]</a>");
						}
						//下一页
						if (pageNow != pageCount) {
							out.println("<a href=kaoqtj.jsp?pageNow="
									+ (pageNow + 1) + ">下一页</a>");
						}
					%>

				</td>
			</tr>
		</table>
	</form>
</body>
</html>
